package com.test;

import com.test.util.DatabaseUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 用户表CRUD测试类
 * 基于MySQL数据库和DatabaseUtil工具类，测试新增、查询、更新、删除操作
 */
public class UserCRUDTest {
    private static final String USER_TABLE = DatabaseUtil.getTableName();

    public static void main(String[] args) {
        try {
            System.out.println("=== 1. 初始化测试环境 ===");
            DatabaseUtil.createTestTable();
            DatabaseUtil.truncateTestTable();
            System.out.println("测试环境初始化完成\n");

            System.out.println("=== 2. 测试新增用户 ===");
            int addId1 = addUser("张三", "zhangsan@test.com", 25);
            int addId2 = addUser("李四", "lisi@test.com", 28);
            System.out.println("新增用户1 ID：" + addId1 + "（姓名：张三）");
            System.out.println("新增用户2 ID：" + addId2 + "（姓名：李四）\n");

            System.out.println("=== 3. 测试查询用户 ===");
            System.out.println("▶ 查询ID=" + addId1 + "的用户：");
            queryUserById(addId1);
            System.out.println("\n▶ 查询所有用户：");
            queryAllUsers();
            System.out.println();

            System.out.println("=== 4. 测试更新用户 ===");
            boolean updateSuccess = updateUser(addId2, "李四", "lisi_update@test.com", 30);
            if (updateSuccess) {
                System.out.println("更新ID=" + addId2 + "的用户成功，更新后信息：");
                queryUserById(addId2);
            } else {
                System.out.println("更新ID=" + addId2 + "的用户失败");
            }
            System.out.println();

            System.out.println("=== 5. 测试删除用户 ===");
            boolean deleteSuccess = deleteUser(addId1);
            if (deleteSuccess) {
                System.out.println("删除ID=" + addId1 + "的用户成功");
            } else {
                System.out.println("删除ID=" + addId1 + "的用户失败");
            }
            System.out.println("\n▶ 删除后剩余用户：");
            queryAllUsers();

        } catch (SQLException e) {
            System.err.println("数据库操作异常：" + e.getMessage());
            e.printStackTrace();
        }
    }

    private static int addUser(String name, String email, int age) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "INSERT INTO " + USER_TABLE + " (name, email, age) VALUES (?, ?, ?)";

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            pstmt.setInt(3, age);

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        } finally {
            DatabaseUtil.closeAll(conn, pstmt, rs);
        }
        return -1;
    }

    private static void queryUserById(int userId) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "SELECT id, name, email, age FROM " + USER_TABLE + " WHERE id = ?";

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                System.out.printf("ID：%d | 姓名：%s | 邮箱：%s | 年龄：%d%n",
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("email"),
                        rs.getInt("age"));
            } else {
                System.out.println("未找到ID=" + userId + "的用户");
            }
        } finally {
            DatabaseUtil.closeAll(conn, pstmt, rs);
        }
    }

    private static void queryAllUsers() throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "SELECT id, name, email, age FROM " + USER_TABLE + " ORDER BY id ASC";

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            int userCount = 0;
            while (rs.next()) {
                System.out.printf("ID：%d | 姓名：%s | 邮箱：%s | 年龄：%d%n",
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("email"),
                        rs.getInt("age"));
                userCount++;
            }
            if (userCount == 0) {
                System.out.println("当前表中无用户数据");
            } else {
                System.out.println("共查询到 " + userCount + " 条用户数据");
            }
        } finally {
            DatabaseUtil.closeAll(conn, pstmt, rs);
        }
    }

    private static boolean updateUser(int userId, String newName, String newEmail, int newAge) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "UPDATE " + USER_TABLE + " SET name = ?, email = ?, age = ? WHERE id = ?";

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, newName);
            pstmt.setString(2, newEmail);
            pstmt.setInt(3, newAge);
            pstmt.setInt(4, userId);

            return pstmt.executeUpdate() > 0;
        } finally {
            DatabaseUtil.closeAll(conn, pstmt, null);
        }
    }

    private static boolean deleteUser(int userId) throws SQLException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        String sql = "DELETE FROM " + USER_TABLE + " WHERE id = ?";

        try {
            conn = DatabaseUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);

            return pstmt.executeUpdate() > 0;
        } finally {
            DatabaseUtil.closeAll(conn, pstmt, null);
        }
    }
}